﻿using Models;
using MovieMS_JW4;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics.Metrics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Car
{
    public class GoodsDAL
    {
        public static List<GoodsModels> selectType(int GoodsTypeID,int count, int pageIndex)
        {
            string sql = string.Format("select top {0} * from Goods where GoodsID " +
                "not in(select top {1} GoodsID from Goods where GoodsTypeID={2}) and GoodsTypeID={3}", count, (count * pageIndex) - count, GoodsTypeID,GoodsTypeID);
            List<GoodsModels> list = new List<GoodsModels>();
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            while (dr.Read())
            {
                GoodsModels gd = new GoodsModels();
                gd.GoodsID = int.Parse(dr["GoodsID"].ToString());
                gd.GoodsName = dr["GoodsName"].ToString();
                gd.GoodsTypeID = int.Parse(dr["GoodsTypeID"].ToString());
                gd.GoodsPrice = decimal.Parse(dr["GoodsPrice"].ToString());
                gd.GoodsDesc = dr["GoodsDesc"].ToString();
                gd.GoodsNum = int.Parse(dr["GoodsNum"].ToString());
                gd.GoodsPic = dr["GoodsPic"].ToString();
                gd.CreateTime = dr["CreateTime"].ToString();

                list.Add(gd);
            }
            dr.Close();
            return list;
        }
        public static List<GoodsModels> selectTypeAll(int GoodsTypeID)
        {
            string sql = string.Format("select * from Goods where GoodsTypeID={0}",GoodsTypeID);
            List<GoodsModels> list = new List<GoodsModels>();
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            while (dr.Read())
            {
                GoodsModels gd = new GoodsModels();
                gd.GoodsID = int.Parse(dr["GoodsID"].ToString());
                gd.GoodsName = dr["GoodsName"].ToString();
                gd.GoodsTypeID = int.Parse(dr["GoodsTypeID"].ToString());
                gd.GoodsPrice = decimal.Parse(dr["GoodsPrice"].ToString());
                gd.GoodsDesc = dr["GoodsDesc"].ToString();
                gd.GoodsNum = int.Parse(dr["GoodsNum"].ToString());
                gd.GoodsPic = dr["GoodsPic"].ToString();
                gd.CreateTime = dr["CreateTime"].ToString();

                list.Add(gd);
            }
            dr.Close();
            return list;
        }
        public static List<GoodsModels> selectByID(int GoodsID)
        {
            string sql = string.Format("select * from Goods where GoodsID={0}", GoodsID);
            List<GoodsModels> list = new List<GoodsModels>();
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            while (dr.Read())
            {
                GoodsModels gd = new GoodsModels();
                gd.GoodsID = int.Parse(dr["GoodsID"].ToString());
                gd.GoodsName = dr["GoodsName"].ToString();
                gd.GoodsTypeID = int.Parse(dr["GoodsTypeID"].ToString());
                gd.GoodsPrice = decimal.Parse(dr["GoodsPrice"].ToString());
                gd.GoodsDesc = dr["GoodsDesc"].ToString();
                gd.GoodsNum = int.Parse(dr["GoodsNum"].ToString());
                gd.GoodsPic = dr["GoodsPic"].ToString();
                gd.CreateTime = dr["CreateTime"].ToString();

                list.Add(gd);
            }
            dr.Close();
            return list;

        }
        public static List<GoodsModels> selectAll()
        {
            string sql = "select * from Goods";
            List<GoodsModels> list = new List<GoodsModels>();
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            while (dr.Read())
            {
                GoodsModels gd = new GoodsModels();
                gd.GoodsID = int.Parse(dr["GoodsID"].ToString());
                gd.GoodsName = dr["GoodsName"].ToString();
                gd.GoodsTypeID = int.Parse(dr["GoodsTypeID"].ToString());
                gd.GoodsPrice = decimal.Parse(dr["GoodsPrice"].ToString());
                gd.GoodsDesc = dr["GoodsDesc"].ToString();
                gd.GoodsNum = int.Parse(dr["GoodsNum"].ToString());
                gd.GoodsPic = dr["GoodsPic"].ToString();
                gd.CreateTime = dr["CreateTime"].ToString();

                list.Add(gd);
            }
            dr.Close();
            return list;
        }
        public static List<GoodsModels> selectBysearch(string GoodsName)
        {
            string sql =string.Format( "select * from Goods where GoodsName like '%{0}%'",GoodsName);
            List<GoodsModels> list = new List<GoodsModels>();
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            while (dr.Read())
            {
                GoodsModels gd = new GoodsModels();
                gd.GoodsID = int.Parse(dr["GoodsID"].ToString());
                gd.GoodsName = dr["GoodsName"].ToString();
                gd.GoodsTypeID = int.Parse(dr["GoodsTypeID"].ToString());
                gd.GoodsPrice = decimal.Parse(dr["GoodsPrice"].ToString());
                gd.GoodsDesc = dr["GoodsDesc"].ToString();
                gd.GoodsNum = int.Parse(dr["GoodsNum"].ToString());
                gd.GoodsPic = dr["GoodsPic"].ToString();
                gd.CreateTime = dr["CreateTime"].ToString();

                list.Add(gd);
            }
            dr.Close();
            return list;
        }
        public static List<GoodsModels> selectBytime(int count, int pageIndex)
        {
            string sql = string.Format("select top {0} * from Goods where GoodsID not in " +
                "(select top {1} GoodsID from Goods) order by CreateTime Desc;", count, (count * pageIndex) - count);
            List<GoodsModels> list = new List<GoodsModels>();
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            while (dr.Read())
            {
                GoodsModels gd = new GoodsModels();
                gd.GoodsID = int.Parse(dr["GoodsID"].ToString());
                gd.GoodsName = dr["GoodsName"].ToString();
                gd.GoodsTypeID = int.Parse(dr["GoodsTypeID"].ToString());
                gd.GoodsPrice = decimal.Parse(dr["GoodsPrice"].ToString());
                gd.GoodsDesc = dr["GoodsDesc"].ToString();
                gd.GoodsNum = int.Parse(dr["GoodsNum"].ToString());
                gd.GoodsPic = dr["GoodsPic"].ToString();
                gd.CreateTime = dr["CreateTime"].ToString();

                list.Add(gd);
            }
            dr.Close();
            return list;
        }
        public static List<GoodsModels> selectByname(string GoodsName,int count,int pageIndex) 
        {
            string sql = string.Format("SELECT top {0}* FROM Goods WHERE GoodsID not in(select top {1} GoodsID from Goods where GoodsName like '%{2}%') " +
                "and GoodsName like '%{3}%'",count,(count*pageIndex)-count,GoodsName, GoodsName);
            //string sql = string.Format("SELECT * FROM Goods WHERE GoodsName LIKE '%{0}%';",GoodsName);
            List<GoodsModels> list = new List<GoodsModels>();
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            while (dr.Read())
            {
                GoodsModels gd = new GoodsModels();
                gd.GoodsID = int.Parse(dr["GoodsID"].ToString());
                gd.GoodsName = dr["GoodsName"].ToString();
                gd.GoodsTypeID = int.Parse(dr["GoodsTypeID"].ToString());
                gd.GoodsPrice = decimal.Parse(dr["GoodsPrice"].ToString());
                gd.GoodsDesc = dr["GoodsDesc"].ToString();
                gd.GoodsNum = int.Parse(dr["GoodsNum"].ToString());
                gd.GoodsPic = dr["GoodsPic"].ToString();
                gd.CreateTime = dr["CreateTime"].ToString();

                list.Add(gd);
            }
            dr.Close();
            return list;
        }
    }
}
